#!/usr/bin/perl

#NOTE: IN ORDER TO USE THIS SCRIPT, YOU MUST FIRST QUERY A SET OF ALL ORDER RECORDS

#THEN YOU MUST "EXPORT" INFORMATION FROM CATALOG
#for instance:

#   RECORD #(ORDER)  

#THE RESULTING TEXT FILE NEEDS TO BE LOADED INTO ./orders_deleted/

use Cwd;

$baseurl = getcwd;

if ($ARGV[0] eq 'CREATING') {
    $mode = 'CREATING';
} else {
    $mode = 'UPDATING';
}

if ($ARGV[1] =~ /\//) {
    ($baseurl = $ARGV[1]) =~ s/[\/]*$//;
}

$error_outs = $baseurl . '/errors_orders_deleted.txt';
$print_outs = $baseurl . '/prints_orders_deleted.txt';

binmode(STDOUT, ':utf8');

open (STDERR, ">$error_outs");
open (STDOUT, ">$print_outs");

print "baseurl: " . $baseurl . "\n";

# local system variables
$connect = $baseurl . '/connect.pl';
require $connect;

use IO::File;

&hashes_and_arrays; #SUBROUTINE AT END OF SCRIPT

($HOUR, $MIN, $SEC) = (localtime)[2, 1, 0];
$start_time = "$HOUR:$MIN:$SEC";

@current_time = localtime;

$last_update_time = ($current_time[5] + 1900) . "-" . ($current_time[4] + 1) . "-$current_time[3] $current_time[2]:$current_time[1]:$current_time[0]";

$where = $baseurl . '/orders_deleted';

##START: GET ORDER NUMBERS OF DELETED RECORDS

use DBI;

# connection for the select
$dbh = DBI->connect("DBI:mysql:database=$db_name;host=$db_host", $db_user, $db_pass, { mysql_enable_utf8 =>1 }) or die "couldn't connect to database: " . DBI->errstr;

$this_table = $db_local_tables_hash{'orders_record_table'};

$first_file_name = $where . '/undeletedorders100000.out';
if(-e $first_file_name) {
        $querystring = "SELECT substr( order_record, 7 )
                        FROM $this_table
                        WHERE order_status = 'x'";

        $query = $dbh->prepare($querystring)
            or die("couldn't prepare statement: ". $dbh->errstr);

        $query->execute()
            or die("couldn't execute statement: ". $dbh->errstr);

        while (@row = $query->fetchrow_array) {
            $already_deleted{$row[0]} = "$row[0]";
        }
}

@current_time = localtime;

##END: GET ORDER NUMBERS OF DELETED RECORDS

###START: UPDATE $this_table TABLE LINES

while (defined($next_file = <$where/*.out>)) {

#print $next_file . "\n";

    open(IN, "< $next_file") || die "cannot open $next_file for reading: $!";

($startoffile = $next_file) =~ s/$where\/undeletedorders//;
$startoffile =~ s/\.out//;
$next_order_record = $startoffile;
$endoffile = $next_order_record + $filesize;


    while(<IN>) {

        if($_ !~ /^RECORD #/) {

        #THIS STARTS WHERE THE DELETED ORDER RECORDS ARE LOADED
        $this_order_record = substr($_, 1, 6);

            while($next_order_record < $this_order_record) {

                if($already_deleted{$next_order_record}) {
                    delete($already_deleted{$next_order_record});
                } else {
                    $update_order_record = $institution . 'o' . $next_order_record;
                    $querystring = "UPDATE $this_table
                                    set order_status = 'x'
                                    WHERE order_record = ?";

                    $query = $dbh->prepare($querystring)
                        or die("couldn't prepare statement: ". $dbh->errstr);
    
                    $query->execute($update_order_record)
                        or die("couldn't execute statement: ". $dbh->errstr);
                }

                $next_order_record++;

            }

        #THIS ENDS WHERE THE DELETED ORDER RECORDS ARE LOADED

        } #END OF if($_ !~ /^RECORD #/)

        $next_order_record++;

        unlink($next_file);

    }

    close(IN) || "cannot close $next_file: $!";

            while ($next_order_record < $endoffile) {

                if($already_deleted{$next_order_record}) {
                    delete($already_deleted{$next_order_record});
                } else {
                $update_order_record = $institution . 'o' . $next_order_record;
                $querystring = "UPDATE $this_table
                                set order_status = 'x'
                                WHERE order_record = ?";

                $query = $dbh->prepare($querystring)
                    or die("couldn't prepare statement: ". $dbh->errstr);

                $query->execute($update_order_record)
                    or die("couldn't execute statement: ". $dbh->errstr);

                }
    
                $next_order_record++;

            }

}

###END: UPDATE $this_table TABLE LINES

####START: CREATING FILES FOR LOAD INTO CONSORTIUM ORDERS TABLES

if ($consortium ne '') {

($HOUR, $MIN, $SEC) = (localtime)[2, 1, 0];
$start_external_update_time = "$HOUR:$MIN:$SEC";

foreach $table_name (order) {

$this_table = $db_local_tables_hash{$table_name . 's_record_table'};

$recordcount = 0;

    $querystring = "SHOW FIELDS FROM $this_table";

        $query = $dbh->prepare($querystring)
            or die("couldn't prepare statement: ". $dbh->errstr);

        $query->execute()
            or die("couldn't execute statement: ". $dbh->errstr);

    while ( @row = $query->fetchrow_array ) {
        push (@fields_array, $row[0]); ## field name
    }

        $query->finish();

pop(@fields_array);

    $fields_array_string = join(", ", @fields_array);

$consortium_keys_textfiles = $baseurl . '/consortium_' . $table_name . 's_textfiles/consortium' . $table_name . 's.key';

$consortium_keys_loader = IO::File->new("> $consortium_keys_textfiles") || die "cannot open $consortium_keys_textfiles for writing: $!\n";
binmode( $consortium_keys_loader, ':utf8' );
flock($consortium_keys_loader, 2);

    print $consortium_keys_loader $fields_array_string . "\n";

        $consortium_keys_loader->autoflush(1);
        close($consortium_keys_loader, 2) || "cannot close $consortium_keys_loader: $!";

$limit_start = 0;
$rownumber = 1;
$status = $table_name . '_status';
while($rownumber > 0) {

        $querystring = "SELECT * from $this_table
            WHERE $this_table.solr_update >= ?
            AND $status = 'x'
            LIMIT ?, 10000";

        $query = $dbh->prepare($querystring)
            or die("couldn't prepare statement: ". $dbh->errstr);

        $query->execute($last_update_time, $limit_start)
            or die("couldn't execute statement: ". $dbh->errstr);

$limit_start += 10000;
$rownumber = $query->rows();

    print "rownumber: " . $rownumber . "\n";

while(@query_array = $query->fetchrow_array()) {
    $int = $recordcount / 10000;

    if ($int =~ m/^\d+$/) {

        if($recordcount > 0) {
        $consortium_batch_loader->autoflush(1);
        close($consortium_batch_loader, 2) || "cannot close $consortium_batch_loader: $!";
        }

$consortium_batch_textfiles = $baseurl . '/consortium_' . $table_name . 's_textfiles/consortium_from_' . $folder_name  . '_' . $table_name . $recordcount .  '.txt';

$consortium_batch_loader = IO::File->new("> $consortium_batch_textfiles") || die "cannot open $consortium_batch_textfiles for writing: $!\n";
binmode( $consortium_batch_loader, ':utf8' );
flock($consortium_batch_loader, 2);

    }

$recordcount++;

pop(@query_array);

foreach(@query_array) {
    s/\t/\\t/g;
}

    $query_array_string = join("\t", @query_array);
    $query_array_string =~ s/\\\t/\\ \t/g;
    $query_array_string =~ s/\n/\\n/g;

    print $consortium_batch_loader $query_array_string . "\n";
    $line_count++;
    }

}

$query->finish();


if($recordcount > 0) {

        $consortium_batch_loader->autoflush(1);
        close($consortium_batch_loader, 2) || "cannot close $consortium_batch_loader: $!";

}

undef(@fields_array);

}

($HOUR, $MIN, $SEC) = (localtime)[2, 1, 0];
$end_external_update_time = "$HOUR:$MIN:$SEC";

}

####END: CREATING FILES FOR LOAD INTO CONSORTIUM ORDERS TABLES

$dbh->disconnect;


($HOUR, $MIN, $SEC) = (localtime)[2, 1, 0];
$end_time = "$HOUR:$MIN:$SEC";

print "Start time: " . $start_time . "\nEnd time: " . $end_time . "\n";

#SUBROUTINES

sub hashes_and_arrays {

$hash_files = $baseurl . '/hashfiles';

while (defined($next_hash_file = <$hash_files/*.pl>)) {
require $next_hash_file;
}

}
